```html
<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>MySQL查询性能优化指南 | 技术小馆</title>
    <link rel="stylesheet" href="https://cdn.staticfile.org/font-awesome/6.4.0/css/all.min.css">
    <link rel="stylesheet" href="https://cdn.staticfile.org/tailwindcss/2.2.19/tailwind.min.css">
    <link href="https://fonts.googleapis.com/css2?family=Noto+Serif+SC:wght@400;500;600;700&family=Noto+Sans+SC:wght@300;400;500;700&display=swap" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/mermaid@latest/dist/mermaid.min.js"></script>
    <style>
        body {
            font-family: 'Noto Sans SC', Tahoma, Arial, Roboto, "Droid Sans", "Helvetica Neue", "Droid Sans Fallback", "Hiragino Sans GB", Simsun, sans-serif;
            line-height: 1.6;
            color: #333;
        }
        h1, h2, h3, h4 {
            font-family: 'Noto Serif SC', serif;
            font-weight: 600;
        }
        .hero-gradient {
            background: linear-gradient(135deg, #6e8efb 0%, #a777e3 100%);
        }
        .card-hover {
            transition: all 0.3s ease;
        }
        .card-hover:hover {
            transform: translateY(-5px);
            box-shadow: 0 10px 25px rgba(0, 0, 0, 0.1);
        }
        .icon-box {
            width: 48px;
            height: 48px;
            display: flex;
            align-items: center;
            justify-content: center;
            border-radius: 12px;
        }
        .section-divider {
            position: relative;
            height: 80px;
            overflow: hidden;
        }
        .section-divider svg {
            position: absolute;
            bottom: 0;
            left: 0;
            width: 100%;
            height: 100%;
        }
        .highlight-box {
            position: relative;
            border-left: 4px solid #4f46e5;
        }
        .hover-underline {
            position: relative;
        }
        .hover-underline:after {
            content: '';
            position: absolute;
            width: 0;
            height: 2px;
            bottom: -2px;
            left: 0;
            background-color: #4f46e5;
            transition: width 0.3s ease;
        }
        .hover-underline:hover:after {
            width: 100%;
        }
        .tooltip {
            position: relative;
            display: inline-block;
        }
        .tooltip .tooltiptext {
            visibility: hidden;
            width: 200px;
            background-color: #555;
            color: #fff;
            text-align: center;
            border-radius: 6px;
            padding: 8px;
            position: absolute;
            z-index: 1;
            bottom: 125%;
            left: 50%;
            margin-left: -100px;
            opacity: 0;
            transition: opacity 0.3s;
        }
        .tooltip:hover .tooltiptext {
            visibility: visible;
            opacity: 1;
        }
    </style>
</head>
<body class="bg-gray-50">
    <!-- Hero Section -->
    <section class="hero-gradient text-white py-20">
        <div class="container mx-auto px-6">
            <div class="max-w-4xl mx-auto text-center">
                <div class="inline-block px-4 py-2 bg-white bg-opacity-20 rounded-full mb-6">
                    <span class="text-sm font-semibold tracking-wider">数据库优化</span>
                </div>
                <h1 class="text-4xl md:text-5xl font-bold mb-6">MySQL查询性能优化指南</h1>
                <p class="text-xl md:text-2xl opacity-90 mb-10 max-w-2xl mx-auto">
                    从表结构设计到系统配置的全面性能优化策略，打造高效数据库系统
                </p>
                <div class="flex justify-center space-x-4">
                    <a href="#content" class="px-6 py-3 bg-white text-indigo-600 font-semibold rounded-lg hover:bg-opacity-90 transition-all duration-300 transform hover:-translate-y-1 shadow-lg">
                        开始探索
                    </a>
                </div>
            </div>
        </div>
    </section>

    <!-- Content Section -->
    <section id="content" class="py-16 bg-white">
        <div class="container mx-auto px-6">
            <!-- Introduction -->
            <div class="max-w-4xl mx-auto mb-16">
                <div class="flex items-center mb-6">
                    <div class="icon-box bg-indigo-100 text-indigo-600 mr-4">
                        <i class="fas fa-database text-xl"></i>
                    </div>
                    <h2 class="text-3xl font-bold text-gray-800">为什么需要优化MySQL查询？</h2>
                </div>
                <p class="text-lg text-gray-700 mb-6">
                    在数据驱动的时代，数据库性能直接影响应用响应速度和用户体验。一个优化良好的MySQL查询可以提升10倍甚至100倍的性能，显著降低服务器负载和运营成本。
                </p>
                <div class="bg-blue-50 border-l-4 border-blue-400 p-4 mb-6">
                    <p class="text-blue-800">
                        <span class="font-semibold">关键指标：</span> 查询延迟从500ms降到50ms，意味着用户等待时间缩短90%，并发处理能力提升10倍！
                    </p>
                </div>
            </div>

            <!-- Visualization -->
            <div class="max-w-5xl mx-auto mb-20">
                <div class="flex items-center mb-6">
                    <div class="icon-box bg-purple-100 text-purple-600 mr-4">
                        <i class="fas fa-project-diagram text-xl"></i>
                    </div>
                    <h2 class="text-3xl font-bold text-gray-800">MySQL优化主要领域</h2>
                </div>
                <div class="mermaid">
                    graph TD
                    A[MySQL优化] --> B[表设计优化]
                    A --> C[查询语句优化]
                    A --> D[索引策略优化]
                    A --> E[锁机制优化]
                    A --> F[分区分表策略]
                    A --> G[系统配置优化]
                    A --> H[硬件优化]
                    B --> B1(数据类型选择)
                    B --> B2(规范化设计)
                    B --> B3(索引设计)
                    C --> C1(减少不必要查询)
                    C --> C2(优化JOIN操作)
                    C --> C3(高效查询条件)
                    D --> D1(索引类型选择)
                    D --> D2(避免过多索引)
                    D --> D3(索引维护)
                </div>
            </div>

            <!-- Main Content -->
            <div class="max-w-4xl mx-auto">
                <!-- 1. 优化表设计 -->
                <div class="mb-16">
                    <div class="flex items-center mb-6">
                        <div class="icon-box bg-green-100 text-green-600 mr-4">
                            <span class="text-xl font-bold">1</span>
                        </div>
                        <h2 class="text-3xl font-bold text-gray-800">优化表设计</h2>
                    </div>
                    
                    <!-- 1.1 选择合适的数据类型 -->
                    <div class="mb-10 pl-14">
                        <h3 class="text-2xl font-semibold text-gray-800 mb-4 flex items-center">
                            <span class="bg-indigo-100 text-indigo-600 rounded-full w-8 h-8 flex items-center justify-center mr-3">1.1</span>
                            选择合适的数据类型
                        </h3>
                        <div class="bg-gray-50 p-6 rounded-lg mb-4">
                            <div class="flex items-start">
                                <div class="text-green-500 mr-3 mt-1">
                                    <i class="fas fa-check-circle"></i>
                                </div>
                                <div>
                                    <p class="text-gray-700 mb-2">使用占用存储空间小的数据类型，减少 I/O 开销</p>
                                    <ul class="list-disc pl-5 text-gray-600 space-y-1">
                                        <li>比如：<code class="bg-gray-200 px-1 rounded">TINYINT</code> 代替 <code class="bg-gray-200 px-1 rounded">INT</code> 存储小范围整数</li>
                                        <li><code class="bg-gray-200 px-1 rounded">VARCHAR</code> 代替 <code class="bg-gray-200 px-1 rounded">TEXT</code> 存储变长字符串</li>
                                    </ul>
                                </div>
                            </div>
                        </div>
                        <div class="bg-gray-50 p-6 rounded-lg">
                            <div class="flex items-start">
                                <div class="text-red-500 mr-3 mt-1">
                                    <i class="fas fa-exclamation-circle"></i>
                                </div>
                                <div>
                                    <p class="text-gray-700">避免使用 <code class="bg-gray-200 px-1 rounded">BLOB</code> 或 <code class="bg-gray-200 px-1 rounded">TEXT</code> 类型，考虑将大字段拆分到其他表</p>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- 1.2 标准化和去冗余 -->
                    <div class="mb-10 pl-14">
                        <h3 class="text-2xl font-semibold text-gray-800 mb-4 flex items-center">
                            <span class="bg-indigo-100 text-indigo-600 rounded-full w-8 h-8 flex items-center justify-center mr-3">1.2</span>
                            标准化和去冗余
                        </h3>
                        <p class="text-gray-700 mb-4">使用数据库规范化（Normalization）减少数据冗余，但对于读多写少的场景，可以适当反规范化以减少联表操作。</p>
                        <div class="grid md:grid-cols-2 gap-6">
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3">规范化设计</h4>
                                <ul class="text-gray-600 space-y-2">
                                    <li class="flex items-start">
                                        <i class="fas fa-check text-green-500 mt-1 mr-2 text-sm"></i>
                                        <span>消除冗余数据</span>
                                    </li>
                                    <li class="flex items-start">
                                        <i class="fas fa-check text-green-500 mt-1 mr-2 text-sm"></i>
                                        <span>减少更新异常</span>
                                    </li>
                                    <li class="flex items-start">
                                        <i class="fas fa-times text-red-500 mt-1 mr-2 text-sm"></i>
                                        <span>可能需要更多JOIN操作</span>
                                    </li>
                                </ul>
                            </div>
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3">反规范化设计</h4>
                                <ul class="text-gray-600 space-y-2">
                                    <li class="flex items-start">
                                        <i class="fas fa-check text-green-500 mt-1 mr-2 text-sm"></i>
                                        <span>减少JOIN操作</span>
                                    </li>
                                    <li class="flex items-start">
                                        <i class="fas fa-check text-green-500 mt-1 mr-2 text-sm"></i>
                                        <span>提高查询性能</span>
                                    </li>
                                    <li class="flex items-start">
                                        <i class="fas fa-times text-red-500 mt-1 mr-2 text-sm"></i>
                                        <span>增加数据冗余</span>
                                    </li>
                                </ul>
                            </div>
                        </div>
                    </div>
                    
                    <!-- 1.3 添加合适的索引 -->
                    <div class="pl-14">
                        <h3 class="text-2xl font-semibold text-gray-800 mb-4 flex items-center">
                            <span class="bg-indigo-100 text-indigo-600 rounded-full w-8 h-8 flex items-center justify-center mr-3">1.3</span>
                            添加合适的索引
                        </h3>
                        <p class="text-gray-700 mb-4">索引是提高查询性能的关键，但需要合理使用。</p>
                        <div class="grid md:grid-cols-3 gap-6 mb-6">
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm hover:border-indigo-300 transition-colors">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-filter text-blue-500 mr-2"></i>
                                    查询条件索引
                                </h4>
                                <p class="text-gray-600">对频繁使用的<code class="bg-gray-100 px-1 rounded">WHERE</code>、<code class="bg-gray-100 px-1 rounded">JOIN</code>、<code class="bg-gray-100 px-1 rounded">GROUP BY</code>、<code class="bg-gray-100 px-1 rounded">ORDER BY</code>字段建立索引</p>
                            </div>
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm hover:border-indigo-300 transition-colors">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-layer-group text-purple-500 mr-2"></i>
                                    覆盖索引
                                </h4>
                                <p class="text-gray-600">优先使用覆盖索引，避免回表查询(<code class="bg-gray-100 px-1 rounded">EXPLAIN</code>中的<code class="bg-gray-100 px-1 rounded">Using index</code>)</p>
                            </div>
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm hover:border-indigo-300 transition-colors">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-cubes text-green-500 mr-2"></i>
                                    复合索引
                                </h4>
                                <p class="text-gray-600">针对多列查询使用复合索引，注意最左前缀原则</p>
                            </div>
                        </div>
                        <div class="bg-yellow-50 border-l-4 border-yellow-400 p-4">
                            <p class="text-yellow-800 flex items-start">
                                <i class="fas fa-exclamation-triangle mt-1 mr-2"></i>
                                <span>索引会增加写入开销，不宜过度使用。定期分析索引使用情况，删除无用索引。</span>
                            </p>
                        </div>
                    </div>
                </div>
                
                <!-- 2. 优化查询语句 -->
                <div class="mb-16">
                    <div class="flex items-center mb-6">
                        <div class="icon-box bg-blue-100 text-blue-600 mr-4">
                            <span class="text-xl font-bold">2</span>
                        </div>
                        <h2 class="text-3xl font-bold text-gray-800">优化查询语句</h2>
                    </div>
                    
                    <!-- 2.1 减少不必要的查询 -->
                    <div class="mb-10 pl-14">
                        <h3 class="text-2xl font-semibold text-gray-800 mb-4 flex items-center">
                            <span class="bg-blue-100 text-blue-600 rounded-full w-8 h-8 flex items-center justify-center mr-3">2.1</span>
                            减少不必要的查询
                        </h3>
                        <div class="grid md:grid-cols-2 gap-6">
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-cut text-red-500 mr-2"></i>
                                    避免检索过多数据
                                </h4>
                                <p class="text-gray-600 mb-3">使用分页查询限制返回的数据量：</p>
                                <div class="bg-gray-800 rounded-lg p-4 mb-3">
                                    <code class="text-gray-300 text-sm">
                                        <span class="text-purple-300">SELECT</span> * <span class="text-purple-300">FROM</span> users 
                                        <span class="text-purple-300">LIMIT</span> <span class="text-yellow-300">10</span> 
                                        <span class="text-purple-300">OFFSET</span> <span class="text-yellow-300">0</span>;
                                    </code>
                                </div>
                                <p class="text-gray-600">对于大表，考虑使用基于游标的分页方式。</p>
                            </div>
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-bullseye text-green-500 mr-2"></i>
                                    精确选择字段
                                </h4>
                                <p class="text-gray-600 mb-3">避免使用<code class="bg-gray-100 px-1 rounded">SELECT *</code>，只返回需要的字段：</p>
                                <div class="bg-gray-800 rounded-lg p-4">
                                    <code class="text-gray-300 text-sm">
                                        <span class="text-purple-300">SELECT</span> id, name, email <span class="text-purple-300">FROM</span> users 
                                        <span class="text-purple-300">WHERE</span> status = <span class="text-yellow-300">1</span>;
                                    </code>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- 2.2 优化 JOIN 操作 -->
                    <div class="mb-10 pl-14">
                        <h3 class="text-2xl font-semibold text-gray-800 mb-4 flex items-center">
                            <span class="bg-blue-100 text-blue-600 rounded-full w-8 h-8 flex items-center justify-center mr-3">2.2</span>
                            优化 JOIN 操作
                        </h3>
                        <div class="grid md:grid-cols-3 gap-6 mb-6">
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-key text-yellow-500 mr-2"></i>
                                    索引关联字段
                                </h4>
                                <p class="text-gray-600">确保关联字段有索引，这是JOIN优化的基础。</p>
                            </div>
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-sort-amount-down text-blue-500 mr-2"></i>
                                    表顺序策略
                                </h4>
                                <p class="text-gray-600">小表驱动大表，将返回较少结果集的表放在前面。</p>
                            </div>
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-minus-circle text-red-500 mr-2"></i>
                                    减少JOIN数量
                                </h4>
                                <p class="text-gray-600">通过反规范化或业务逻辑调整减少JOIN表数量。</p>
                            </div>
                        </div>
                        <div class="bg-gray-800 rounded-lg p-4 mb-4">
                            <code class="text-gray-300 text-sm">
                                <span class="text-purple-300">-- 优化前：无索引JOIN</span><br>
                                <span class="text-purple-300">SELECT</span> * <span class="text-purple-300">FROM</span> orders<br>
                                <span class="text-purple-300">JOIN</span> users <span class="text-purple-300">ON</span> orders.user_id = users.id;<br><br>
                                
                                <span class="text-purple-300">-- 优化后：确保user_id和id都有索引</span><br>
                                <span class="text-purple-300">SELECT</span> orders.id, orders.amount, users.name <span class="text-purple-300">FROM</span> orders<br>
                                <span class="text-purple-300">JOIN</span> users <span class="text-purple-300">ON</span> orders.user_id = users.id<br>
                                <span class="text-purple-300">WHERE</span> users.status = <span class="text-yellow-300">'active'</span>;
                            </code>
                        </div>
                    </div>
                    
                    <!-- 2.3 使用高效的查询条件 -->
                    <div class="mb-10 pl-14">
                        <h3 class="text-2xl font-semibold text-gray-800 mb-4 flex items-center">
                            <span class="bg-blue-100 text-blue-600 rounded-full w-8 h-8 flex items-center justify-center mr-3">2.3</span>
                            使用高效的查询条件
                        </h3>
                        <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm mb-6">
                            <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                <i class="fas fa-exclamation-triangle text-red-500 mr-2"></i>
                                避免索引失效的操作
                            </h4>
                            <p class="text-gray-600 mb-4">在<code class="bg-gray-100 px-1 rounded">WHERE</code>子句中对索引字段使用函数或表达式会导致索引失效：</p>
                            <div class="grid md:grid-cols-2 gap-6">
                                <div>
                                    <p class="text-gray-600 font-medium mb-2">错误示例：</p>
                                    <div class="bg-gray-800 rounded-lg p-4">
                                        <code class="text-gray-300 text-sm">
                                            <span class="text-purple-300">SELECT</span> * <span class="text-purple-300">FROM</span> users <br>
                                            <span class="text-purple-300">WHERE</span> <span class="text-blue-300">YEAR</span>(created_at) = <span class="text-yellow-300">2023</span>;
                                        </code>
                                    </div>
                                </div>
                                <div>
                                    <p class="text-gray-600 font-medium mb-2">优化后：</p>
                                    <div class="bg-gray-800 rounded-lg p-4">
                                        <code class="text-gray-300 text-sm">
                                            <span class="text-purple-300">SELECT</span> * <span class="text-purple-300">FROM</span> users <br>
                                            <span class="text-purple-300">WHERE</span> created_at <span class="text-purple-300">BETWEEN</span> <span class="text-yellow-300">'2023-01-01'</span> <br>
                                            <span class="text-purple-300">AND</span> <span class="text-yellow-300">'2023-12-31'</span>;
                                        </code>
                                    </div>
                                </div>
                            </div>
                        </div>
                        <div class="bg-green-50 border-l-4 border-green-400 p-4">
                            <p class="text-green-800 flex items-start">
                                <i class="fas fa-lightbulb mt-1 mr-2"></i>
                                <span>使用<code class="bg-green-100 px-1 rounded">EXPLAIN</code>分析查询计划，确保查询使用了索引(<code class="bg-green-100 px-1 rounded">Using index</code>)。对于复杂查询，考虑使用查询重写或拆分为多个简单查询。</span>
                            </p>
                        </div>
                    </div>
                    
                    <!-- 2.4 利用查询缓存 -->
                    <div class="pl-14">
                        <h3 class="text-2xl font-semibold text-gray-800 mb-4 flex items-center">
                            <span class="bg-blue-100 text-blue-600 rounded-full w-8 h-8 flex items-center justify-center mr-3">2.4</span>
                            利用查询缓存
                        </h3>
                        <div class="grid md:grid-cols-2 gap-6">
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-database text-purple-500 mr-2"></i>
                                    MySQL查询缓存
                                </h4>
                                <p class="text-gray-600 mb-3">在高频查询但更新较少的场景下，可以使用MySQL查询缓存(如果支持)：</p>
                                <div class="bg-gray-800 rounded-lg p-4">
                                    <code class="text-gray-300 text-sm">
                                        <span class="text-purple-300">SET</span> <span class="text-blue-300">GLOBAL</span> query_cache_size = <span class="text-yellow-300">67108864</span>; <span class="text-gray-500">-- 64MB</span><br>
                                        <span class="text-purple-300">SET</span> <span class="text-blue-300">GLOBAL</span> query_cache_type = <span class="text-yellow-300">1</span>;
                                    </code>
                                </div>
                                <p class="text-gray-600 mt-3 text-sm">注意：MySQL 8.0已移除查询缓存功能。</p>
                            </div>
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-memory text-blue-500 mr-2"></i>
                                    应用层缓存
                                </h4>
                                <p class="text-gray-600 mb-3">使用Redis、Memcached等缓存高频查询结果：</p>
                                <div class="bg-gray-800 rounded-lg p-4">
                                    <code class="text-gray-300 text-sm">
                                        <span class="text-gray-500">// 伪代码示例</span><br>
                                        cached_result = cache.<span class="text-blue-300">get</span>(<span class="text-yellow-300">"user:123:profile"</span>);<br>
                                        <span class="text-purple-300">if</span> (!cached_result) {<br>
                                        &nbsp;&nbsp;cached_result = db.<span class="text-blue-300">query</span>(<span class="text-yellow-300">"SELECT * FROM users WHERE id = 123"</span>);<br>
                                        &nbsp;&nbsp;cache.<span class="text-blue-300">set</span>(<span class="text-yellow-300">"user:123:profile"</span>, cached_result, <span class="text-yellow-300">3600</span>);<br>
                                        }<br>
                                        <span class="text-purple-300">return</span> cached_result;
                                    </code>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
                
                <!-- 3. 优化索引策略 -->
                <div class="mb-16">
                    <div class="flex items-center mb-6">
                        <div class="icon-box bg-purple-100 text-purple-600 mr-4">
                            <span class="text-xl font-bold">3</span>
                        </div>
                        <h2 class="text-3xl font-bold text-gray-800">优化索引策略</h2>
                    </div>
                    
                    <!-- 3.1 优化索引类型 -->
                    <div class="mb-10 pl-14">
                        <h3 class="text-2xl font-semibold text-gray-800 mb-4 flex items-center">
                            <span class="bg-purple-100 text-purple-600 rounded-full w-8 h-8 flex items-center justify-center mr-3">3.1</span>
                            优化索引类型
                        </h3>
                        <div class="grid md:grid-cols-2 gap-6 mb-6">
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-bullseye text-green-500 mr-2"></i>
                                    高选择性字段索引
                                </h4>
                                <p class="text-gray-600">对区分度高(基数大)的字段添加索引更有效，如用户ID、手机号等。</p>
                                <div class="mt-4">
                                    <p class="text-sm text-gray-500 mb-2">选择性计算公式：</p>
                                    <code class="bg-gray-100 px-2 py-1 rounded text-sm">
                                        SELECT COUNT(DISTINCT column)/COUNT(*) FROM table;
                                    </code>
                                </div>
                            </div>
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-cogs text-blue-500 mr-2"></i>
                                    索引类型选择
                                </h4>
                                <ul class="text-gray-600 space-y-2">
                                    <li><span class="font-medium">B-Tree索引：</span> 默认类型，适合范围查询(=, >, <, BETWEEN, LIKE 'prefix%')</li>
                                    <li><span class="font-medium">哈希索引：</span> 仅Memory引擎支持，适合精确匹配(=, <=>)</li>
                                    <li><span class="font-medium">全文索引：</span> 用于全文搜索</li>
                                </ul>
                            </div>
                        </div>
                    </div>
                    
                    <!-- 3.2 避免过多索引 -->
                    <div class="mb-10 pl-14">
                        <h3 class="text-2xl font-semibold text-gray-800 mb-4 flex items-center">
                            <span class="bg-purple-100 text-purple-600 rounded-full w-8 h-8 flex items-center justify-center mr-3">3.2</span>
                            避免过多索引
                        </h3>
                        <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm mb-4">
                            <p class="text-gray-700 mb-4">每个索引都会增加写操作的开销，应根据实际查询模式合理添加索引。</p>
                            <div class="grid md:grid-cols-3 gap-4">
                                <div class="bg-red-50 p-4 rounded-lg">
                                    <h4 class="font-semibold text-red-700 mb-2 flex items-center">
                                        <i class="fas fa-times-circle mr-2"></i>
                                        索引开销
                                    </h4>
                                    <ul class="text-red-600 text-sm space-y-1">
                                        <li>增加INSERT/UPDATE/DELETE时间</li>
                                        <li>占用额外存储空间</li>
                                        <li>增加优化器选择时间</li>
                                    </ul>
                                </div>
                                <div class="bg-blue-50 p-4 rounded-lg">
                                    <h4 class="font-semibold text-blue-700 mb-2 flex items-center">
                                        <i class="fas fa-search mr-2"></i>
                                        索引分析
                                    </h4>
                                    <ul class="text-blue-600 text-sm space-y-1">
                                        <li>使用<code class="bg-blue-100 px-1 rounded">SHOW INDEX FROM table</code></li>
                                        <li>分析<code class="bg-blue-100 px-1 rounded">Cardinality</code>值</li>
                                        <li>监控索引使用情况</li>
                                    </ul>
                                </div>
                                <div class="bg-green-50 p-4 rounded-lg">
                                    <h4 class="font-semibold text-green-700 mb-2 flex items-center">
                                        <i class="fas fa-trash-alt mr-2"></i>
                                        索引清理
                                    </h4>
                                    <ul class="text-green-600 text-sm space-y-1">
                                        <li>删除未使用的索引</li>
                                        <li>合并重复索引</li>
                                        <li>定期优化表</li>
                                    </ul>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- 3.3 定期维护索引 -->
                    <div class="pl-14">
                        <h3 class="text-2xl font-semibold text-gray-800 mb-4 flex items-center">
                            <span class="bg-purple-100 text-purple-600 rounded-full w-8 h-8 flex items-center justify-center mr-3">3.3</span>
                            定期维护索引
                        </h3>
                        <div class="grid md:grid-cols-2 gap-6">
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-chart-line text-blue-500 mr-2"></i>
                                    更新统计信息
                                </h4>
                                <p class="text-gray-600 mb-3">执行<code class="bg-gray-100 px-1 rounded">ANALYZE TABLE</code>更新索引统计信息，帮助优化器做出更好的决策：</p>
                                <div class="bg-gray-800 rounded-lg p-4">
                                    <code class="text-gray-300 text-sm">
                                        <span class="text-purple-300">ANALYZE</span> <span class="text-blue-300">TABLE</span> users;
                                    </code>
                                </div>
                            </div>
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-broom text-green-500 mr-2"></i>
                                    优化表存储
                                </h4>
                                <p class="text-gray-600 mb-3">对长期使用的表执行<code class="bg-gray-100 px-1 rounded">OPTIMIZE TABLE</code>整理碎片：</p>
                                <div class="bg-gray-800 rounded-lg p-4">
                                    <code class="text-gray-300 text-sm">
                                        <span class="text-purple-300">OPTIMIZE</span> <span class="text-blue-300">TABLE</span> large_table;
                                    </code>
                                </div>
                                <p class="text-gray-600 mt-3 text-sm">注意：此操作会锁表，应在低峰期执行。</p>
                            </div>
                        </div>
                    </div>
                </div>
                
                <!-- 4. 优化锁机制 -->
                <div class="mb-16">
                    <div class="flex items-center mb-6">
                        <div class="icon-box bg-red-100 text-red-600 mr-4">
                            <span class="text-xl font-bold">4</span>
                        </div>
                        <h2 class="text-3xl font-bold text-gray-800">优化锁机制</h2>
                    </div>
                    
                    <div class="pl-14">
                        <div class="grid md:grid-cols-3 gap-6 mb-6">
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-layer-group text-blue-500 mr-2"></i>
                                    事务隔离级别
                                </h4>
                                <p class="text-gray-600">使用合适的隔离级别(如<code class="bg-gray-100 px-1 rounded">READ COMMITTED</code>)，平衡一致性和并发性。</p>
                                <div class="mt-4">
                                    <code class="bg-gray-800 text-gray-300 text-sm p-2 rounded block">
                                        <span class="text-purple-300">SET</span> <span class="text-blue-300">TRANSACTION</span> <span class="text-purple-300">ISOLATION LEVEL</span> READ COMMITTED;
                                    </code>
                                </div>
                            </div>
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-lock text-red-500 mr-2"></i>
                                    锁定范围控制
                                </h4>
                                <p class="text-gray-600">尽量减少锁定范围，精确锁定需要的行。</p>
                                <div class="mt-4">
                                    <code class="bg-gray-800 text-gray-300 text-sm p-2 rounded block">
                                        <span class="text-purple-300">-- 只锁定符合条件的行</span><br>
                                        <span class="text-purple-300">SELECT</span> * <span class="text-purple-300">FROM</span> orders <br>
                                        <span class="text-purple-300">WHERE</span> user_id = <span class="text-yellow-300">123</span> <span class="text-purple-300">FOR</span> <span class="text-blue-300">UPDATE</span>;
                                    </code>
                                </div>
                            </div>
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-code-branch text-purple-500 mr-2"></i>
                                    事务拆分
                                </h4>
                                <p class="text-gray-600">将大事务拆分为多个小事务，减少锁持有时间。</p>
                                <div class="mt-4">
                                    <code class="bg-gray-800 text-gray-300 text-sm p-2 rounded block">
                                        <span class="text-purple-300">-- 避免</span><br>
                                        <span class="text-purple-300">BEGIN</span>;<br>
                                        <span class="text-purple-300">UPDATE</span> large_table <span class="text-purple-300">SET</span> ... ; <span class="text-gray-500">/* 耗时操作 */</span><br>
                                        <span class="text-purple-300">COMMIT</span>;
                                    </code>
                                </div>
                            </div>
                        </div>
                        
                        <div class="bg-yellow-50 border-l-4 border-yellow-400 p-4">
                            <p class="text-yellow-800 flex items-start">
                                <i class="fas fa-exclamation-triangle mt-1 mr-2"></i>
                                <span>使用<code class="bg-yellow-100 px-1 rounded">SHOW ENGINE INNODB STATUS</code>监控锁等待情况，及时发现和解决锁冲突问题。</span>
                            </p>
                        </div>
                    </div>
                </div>
                
                <!-- 5. 使用分区和分库分表 -->
                <div class="mb-16">
                    <div class="flex items-center mb-6">
                        <div class="icon-box bg-indigo-100 text-indigo-600 mr-4">
                            <span class="text-xl font-bold">5</span>
                        </div>
                        <h2 class="text-3xl font-bold text-gray-800">使用分区和分库分表</h2>
                    </div>
                    
                    <div class="pl-14">
                        <div class="grid md:grid-cols-3 gap-6 mb-8">
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-table text-blue-500 mr-2"></i>
                                    水平分表
                                </h4>
                                <p class="text-gray-600">将数据按规则(如用户ID哈希)拆分到多个表中，减少单表数据量。</p>
                                <div class="mt-4">
                                    <p class="text-sm text-gray-500 mb-1">示例表名：</p>
                                    <ul class="text-sm text-gray-600">
                                        <li>users_0</li>
                                        <li>users_1</li>
                                        <li>users_2</li>
                                    </ul>
                                </div>
                            </div>
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-server text-purple-500 mr-2"></i>
                                    数据库分片
                                </h4>
                                <p class="text-gray-600">将数据按业务拆分到多个数据库中，提高写入和查询性能。</p>
                                <div class="mt-4">
                                    <p class="text-sm text-gray-500 mb-1">常见分片策略：</p>
                                    <ul class="text-sm text-gray-600">
                                        <li>按用户ID范围</li>
                                        <li>按地理位置</li>
                                        <li>按时间周期</li>
                                    </ul>
                                </div>
                            </div>
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-project-diagram text-green-500 mr-2"></i>
                                    分区表
                                </h4>
                                <p class="text-gray-600">使用MySQL分区功能优化大表性能。</p>
                                <div class="mt-4">
                                    <code class="bg-gray-800 text-gray-300 text-sm p-2 rounded block">
                                        <span class="text-purple-300">CREATE</span> <span class="text-blue-300">TABLE</span> logs (<br>
                                        &nbsp;&nbsp;id <span class="text-blue-300">INT</span>,<br>
                                        &nbsp;&nbsp;log_date <span class="text-blue-300">DATE</span><br>
                                        ) <span class="text-purple-300">PARTITION</span> <span class="text-blue-300">BY</span> <span class="text-purple-300">RANGE</span>(<span class="text-blue-300">YEAR</span>(log_date)) (<br>
                                        &nbsp;&nbsp;<span class="text-purple-300">PARTITION</span> p2020 <span class="text-purple-300">VALUES</span> <span class="text-blue-300">LESS THAN</span> (<span class="text-yellow-300">2021</span>),<br>
                                        &nbsp;&nbsp;<span class="text-purple-300">PARTITION</span> p2021 <span class="text-purple-300">VALUES</span> <span class="text-blue-300">LESS THAN</span> (<span class="text-yellow-300">2022</span>),<br>
                                        &nbsp;&nbsp;<span class="text-purple-300">PARTITION</span> pmax <span class="text-purple-300">VALUES</span> <span class="text-blue-300">LESS THAN</span> <span class="text-purple-300">MAXVALUE</span><br>
                                        );
                                    </code>
                                </div>
                            </div>
                        </div>
                        
                        <div class="bg-blue-50 border-l-4 border-blue-400 p-4">
                            <p class="text-blue-800 flex items-start">
                                <i class="fas fa-info-circle mt-1 mr-2"></i>
                                <span>分库分表会增加系统复杂度，需要考虑跨分片查询、事务一致性等问题。对于大多数应用，建议先考虑其他优化手段，当单表数据量超过千万级别时再考虑分库分表。</span>
                            </p>
                        </div>
                    </div>
                </div>
                
                <!-- 6. 查询分析与监控 -->
                <div class="mb-16">
                    <div class="flex items-center mb-6">
                        <div class="icon-box bg-yellow-100 text-yellow-600 mr-4">
                            <span class="text-xl font-bold">6</span>
                        </div>
                        <h2 class="text-3xl font-bold text-gray-800">查询分析与监控</h2>
                    </div>
                    
                    <div class="pl-14">
                        <div class="grid md:grid-cols-3 gap-6 mb-8">
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-search text-blue-500 mr-2"></i>
                                    EXPLAIN分析
                                </h4>
                                <p class="text-gray-600">使用<code class="bg-gray-100 px-1 rounded">EXPLAIN</code>分析查询执行计划，查看是否使用了索引。</p>
                                <div class="mt-4">
                                    <code class="bg-gray-800 text-gray-300 text-sm p-2 rounded block">
                                        <span class="text-purple-300">EXPLAIN</span> <span class="text-purple-300">SELECT</span> * <span class="text-purple-300">FROM</span> users <span class="text-purple-300">WHERE</span> id = <span class="text-yellow-300">123</span>;
                                    </code>
                                </div>
                                <p class="text-gray-600 mt-3 text-sm">关键字段：type, key, rows, Extra</p>
                            </div>
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-tachometer-alt text-red-500 mr-2"></i>
                                    慢查询日志
                                </h4>
                                <p class="text-gray-600">开启慢查询日志，定位性能瓶颈。</p>
                                <div class="mt-4">
                                    <code class="bg-gray-800 text-gray-300 text-sm p-2 rounded block">
                                        <span class="text-purple-300">SET</span> <span class="text-blue-300">GLOBAL</span> slow_query_log = <span class="text-yellow-300">'ON'</span>;<br>
                                        <span class="text-purple-300">SET</span> <span class="text-blue-300">GLOBAL</span> long_query_time = <span class="text-yellow-300">1</span>; <span class="text-gray-500">-- 1秒</span><br>
                                        <span class="text-purple-300">SET</span> <span class="text-blue-300">GLOBAL</span> slow_query_log_file = <span class="text-yellow-300">'/var/log/mysql/mysql-slow.log'</span>;
                                    </code>
                                </div>
                            </div>
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-chart-pie text-purple-500 mr-2"></i>
                                    性能监控工具
                                </h4>
                                <p class="text-gray-600">使用工具跟踪和分析查询性能。</p>
                                <div class="mt-4">
                                    <ul class="text-sm text-gray-600 space-y-2">
                                        <li class="flex items-start">
                                            <i class="fas fa-cube text-blue-500 mt-1 mr-2"></i>
                                            <span>MySQL Performance Schema</span>
                                        </li>
                                        <li class="flex items-start">
                                            <i class="fas fa-wrench text-green-500 mt-1 mr-2"></i>
                                            <span>pt-query-digest</span>
                                        </li>
                                        <li class="flex items-start">
                                            <i class="fas fa-desktop text-red-500 mt-1 mr-2"></i>
                                            <span>Zabbix/Prometheus/Grafana</span>
                                        </li>
                                    </ul>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
                
                <!-- 7. 硬件与配置优化 -->
                <div class="mb-16">
                    <div class="flex items-center mb-6">
                        <div class="icon-box bg-green-100 text-green-600 mr-4">
                            <span class="text-xl font-bold">7</span>
                        </div>
                        <h2 class="text-3xl font-bold text-gray-800">硬件与配置优化</h2>
                    </div>
                    
                    <div class="pl-14">
                        <div class="grid md:grid-cols-2 gap-6 mb-8">
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-sliders-h text-blue-500 mr-2"></i>
                                    配置优化
                                </h4>
                                <p class="text-gray-600 mb-3">调整关键MySQL配置参数：</p>
                                <div class="bg-gray-800 rounded-lg p-4">
                                    <code class="text-gray-300 text-sm">
                                        <span class="text-purple-300"># InnoDB缓冲池 (建议为物理内存的50-70%)</span><br>
                                        innodb_buffer_pool_size = 4G<br><br>
                                        
                                        <span class="text-purple-300"># 最大连接数 (根据应用需求调整)</span><br>
                                        max_connections = 200<br><br>
                                        
                                        <span class="text-purple-300"># 查询缓存 (MySQL 8.0之前)</span><br>
                                        query_cache_size = 64M<br>
                                        query_cache_type = 1
                                    </code>
                                </div>
                            </div>
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-microchip text-purple-500 mr-2"></i>
                                    硬件优化
                                </h4>
                                <div class="grid grid-cols-2 gap-4">
                                    <div class="bg-blue-50 p-4 rounded-lg">
                                        <h5 class="font-semibold text-blue-700 mb-2 flex items-center">
                                            <i class="fas fa-memory mr-2"></i>
                                            内存
                                        </h5>
                                        <p class="text-blue-600 text-sm">增加内存，确保活跃数据集能放入内存。</p>
                                    </div>
                                    <div class="bg-green-50 p-4 rounded-lg">
                                        <h5 class="font-semibold text-green-700 mb-2 flex items-center">
                                            <i class="fas fa-hdd mr-2"></i>
                                            SSD存储
                                        </h5>
                                        <p class="text-green-600 text-sm">使用SSD减少磁盘I/O延迟。</p>
                                    </div>
                                    <div class="bg-yellow-50 p-4 rounded-lg">
                                        <h5 class="font-semibold text-yellow-700 mb-2 flex items-center">
                                            <i class="fas fa-microchip mr-2"></i>
                                            CPU核心
                                        </h5>
                                        <p class="text-yellow-600 text-sm">多核CPU处理并发查询。</p>
                                    </div>
                                    <div class="bg-red-50 p-4 rounded-lg">
                                        <h5 class="font-semibold text-red-700 mb-2 flex items-center">
                                            <i class="fas fa-network-wired mr-2"></i>
                                            网络
                                        </h5>
                                        <p class="text-red-600 text-sm">高带宽、低延迟网络连接。</p>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
                
                <!-- 8. 结合业务场景的优化 -->
                <div>
                    <div class="flex items-center mb-6">
                        <div class="icon-box bg-indigo-100 text-indigo-600 mr-4">
                            <span class="text-xl font-bold">8</span>
                        </div>
                        <h2 class="text-3xl font-bold text-gray-800">结合业务场景的优化</h2>
                    </div>
                    
                    <div class="pl-14">
                        <div class="grid md:grid-cols-2 gap-6 mb-8">
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-chart-bar text-blue-500 mr-2"></i>
                                    统计类操作优化
                                </h4>
                                <p class="text-gray-600 mb-3">对于复杂统计查询，考虑使用以下策略：</p>
                                <ul class="text-gray-600 space-y-2">
                                    <li class="flex items-start">
                                        <i class="fas fa-check text-green-500 mt-1 mr-2"></i>
                                        <span>预计算统计结果，存储在物化视图或缓存表中</span>
                                    </li>
                                    <li class="flex items-start">
                                        <i class="fas fa-check text-green-500 mt-1 mr-2"></i>
                                        <span>定时任务更新统计结果，避免实时计算</span>
                                    </li>
                                    <li class="flex items-start">
                                        <i class="fas fa-check text-green-500 mt-1 mr-2"></i>
                                        <span>使用汇总表存储常用统计指标</span>
                                    </li>
                                </ul>
                            </div>
                            <div class="bg-white border border-gray-200 rounded-lg p-6 shadow-sm">
                                <h4 class="font-semibold text-lg text-gray-800 mb-3 flex items-center">
                                    <i class="fas fa-bolt text-yellow-500 mr-2"></i>
                                    高频查询缓存
                                </h4>
                                <p class="text-gray-600 mb-3">对高频但更新较少的数据，使用缓存策略：</p>
                                <ul class="text-gray-600 space-y-2">
                                    <li class="flex items-start">
                                        <i class="fas fa-database text-blue-500 mt-1 mr-2"></i>
                                        <span>MySQL查询缓存(8.0之前)</span>
                                    </li>
                                    <li class="flex items-start">
                                        <i class="fas fa-memory text-purple-500 mt-1 mr-2"></i>
                                        <span>应用层缓存(Redis/Memcached)</span>
                                    </li>
                                    <li class="flex items-start">
                                        <i class="fas fa-sync-alt text-green-500 mt-1 mr-2"></i>
                                        <span>异步更新缓存，避免缓存击穿</span>
                                    </li>
                                </ul>
                            </div>
                        </div>
                        
                        <div class="bg-purple-50 border-l-4 border-purple-400 p-4">
                            <p class="text-purple-800 flex items-start">
                                <i class="fas fa-lightbulb mt-1 mr-2"></i>
                                <span>最佳的性能优化策略是结合具体业务场景和数据特征制定的。建议先分析业务查询模式，找出性能瓶颈，再针对性地应用适当的优化技术。</span>
                            </p>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </section>

    <!-- Footer -->
    <footer class="bg-gray-900 text-gray-300 py-12">
        <div class="container mx-auto px-6">
            <div class="max-w-4xl mx-auto text-center">
                <h3 class="text-2xl font-bold text-white mb-4">技术小馆</h3>
                <p class="mb-6">探索技术奥秘，分享专业知识</p>
                <div class="flex justify-center space-x-4 mb-6">
                    <a href="http://www.yuque.com/jtostring" class="hover:text-white hover-underline">
                        <i class="fas fa-globe mr-1"></i> 语雀主页
                    </a>
                </div>
                <p class="text-sm text-gray-500">© 2023 技术小馆. 保留所有权利.</p>
            </div>
        </div>
    </footer>

    <script>
        mermaid.initialize({
            startOnLoad: true,
            theme: 'default',
            flowchart: {
                useMaxWidth: true,
                htmlLabels: true,
                curve: 'basis'
            }
        });
    </script>
</body>
</html>
```